package ee.maoinvest.backend.model.dao.postgresqlimplementations;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import ee.maoinvest.backend.database.ConnectionManager;
import ee.maoinvest.backend.model.dao.ElectricityMeterIncludedInRegularReadingDAO;
import ee.maoinvest.backend.model.data.ElectricityMeterIncludedInRegularReading;

public class ElectricityMeterIncludedInRegularReadingDAOPostgresqlImpl implements ElectricityMeterIncludedInRegularReadingDAO
{
	
	Logger logger = Logger.getLogger(ElectricityMeterIncludedInRegularReadingDAOPostgresqlImpl.class);
	Connection dbConnection;
	String sql;
	PreparedStatement preparedStatement;
	Statement statement;
	ResultSet resultSet;
	private final int WARNING_NO_ROWS_AFFECTED = 0;
	private int rowsAffected;
	
	public ElectricityMeterIncludedInRegularReadingDAOPostgresqlImpl(Connection dbConnection)
	{
		this.dbConnection = dbConnection;
	}
	

	@Override
	public int insertElectricityMeterIncludedInRegularReading(
			ElectricityMeterIncludedInRegularReading electricityMeterIncludedInRegularReading) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "INSERT INTO Electricity_meters_included_in_regular_reading " + 
				"(regularReadingFK, electricityMeterFK, electricityMeterReadingFK, hasReadingBeenTaken) " + 
				"VALUES (?, ?, ?, ?);";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			
			preparedStatement.setInt	(1, electricityMeterIncludedInRegularReading.getRegularReadingFK());
			preparedStatement.setInt	(2, electricityMeterIncludedInRegularReading.getElectricityMeterFK());
			preparedStatement.setObject	(3, electricityMeterIncludedInRegularReading.getElectricityMeterReadingFK(), Types.INTEGER);
			preparedStatement.setBoolean(4, electricityMeterIncludedInRegularReading.getHasReadingBeenTaken());
			
			logger.info(preparedStatement.toString());
			rowsAffected = preparedStatement.executeUpdate();
			
			if (rowsAffected == 1)
			{
				return rowsAffected;
			}
			else
			{
				System.out.println("No rows affected!");
				logger.info("Unsuccessful insertion to a database: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			return WARNING_NO_ROWS_AFFECTED;
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public ElectricityMeterIncludedInRegularReading getElectricityMeterIncludedInRegularReadingByFKs(
			int regularReadingFK, int electricityMeterFK) 
	{
		resultSet = null;
		ElectricityMeterIncludedInRegularReading readingIncludedInRegularReadingRetrieved = null;
		sql = "SELECT * FROM Electricity_meters_included_in_regular_reading WHERE regularReadingFK = ? AND electricityMeterFK = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
						sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY
					);
			//filling the placeholders 
			preparedStatement.setInt(1, regularReadingFK);
			preparedStatement.setInt(2, electricityMeterFK);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of a received data set (so it can start to read and process data)
			resultSet.beforeFirst();
			
			if (resultSet.next())
			{
				//setObject() is used, when column or field value in java, can also be null.
				readingIncludedInRegularReadingRetrieved = fillReadingIncludedInRegularReadingWithDataRetrievedFromDB();
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return readingIncludedInRegularReadingRetrieved;
	}

	@Override
	public int updateElectricityMeterIncludedInRegularReading(
			ElectricityMeterIncludedInRegularReading electricityMeterIncludedInRegularReading) 
	{
		resultSet = null;
		rowsAffected = WARNING_NO_ROWS_AFFECTED;
		sql = "UPDATE Electricity_meters_included_in_regular_reading " +
				"SET regularReadingFK = ?, electricityMeterFK = ?, electricityMeterReadingFK = ?, hasReadingBeenTaken = ? " +
				"WHERE regularReadingFK = ? AND electricityMeterFK = ?;"; 
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(sql);
			
			preparedStatement.setInt	(1, electricityMeterIncludedInRegularReading.getRegularReadingFK());
			preparedStatement.setInt	(2, electricityMeterIncludedInRegularReading.getElectricityMeterFK());
			preparedStatement.setObject	(3, electricityMeterIncludedInRegularReading.getElectricityMeterReadingFK(), Types.INTEGER);
			preparedStatement.setBoolean(4, electricityMeterIncludedInRegularReading.getHasReadingBeenTaken());
			preparedStatement.setInt	(5, electricityMeterIncludedInRegularReading.getRegularReadingFK());
			preparedStatement.setInt	(6, electricityMeterIncludedInRegularReading.getElectricityMeterFK());
			
			rowsAffected = preparedStatement.executeUpdate();
			
			
			if (rowsAffected == 1)
			{
				logger.info("Update sucessful: " + preparedStatement.toString());
				return rowsAffected;
			}
			else
			{
				logger.error("Update was unsuccesful: " + preparedStatement.toString());
				return WARNING_NO_ROWS_AFFECTED;
			}
		}
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
			return WARNING_NO_ROWS_AFFECTED;		
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
	}

	@Override
	public List<ElectricityMeterIncludedInRegularReading> getAllElectricityMetersIncludedInRegularReadingByRegularReadingID(
			int regularReadingID) 
	{
		List<ElectricityMeterIncludedInRegularReading> listOfAllReadingsIncludedInRegularReadingByRegularReadingID = new ArrayList<ElectricityMeterIncludedInRegularReading>();
		sql = "SELECT * FROM Electricity_meters_included_in_regular_reading WHERE regularReadingFK = ?;";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, regularReadingID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				listOfAllReadingsIncludedInRegularReadingByRegularReadingID.add(fillReadingIncludedInRegularReadingWithDataRetrievedFromDB());
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfAllReadingsIncludedInRegularReadingByRegularReadingID;
	}
	
	@Override
	public List<ElectricityMeterIncludedInRegularReading> getAllElectricityMetersIncludedInRegularReadingWhichReadingsHaveNotBeenTakenYetByRegularReadingID(
			int regularReadingID) 
	{
		List<ElectricityMeterIncludedInRegularReading> listOfAllReadingsIncludedInRegularReadingByRegularReadingID = new ArrayList<ElectricityMeterIncludedInRegularReading>();
		sql = "SELECT * FROM Electricity_meters_included_in_regular_reading WHERE regularReadingFK = ? AND hasReadingBeenTaken = false";
		
		try 
		{
			preparedStatement = dbConnection.prepareStatement(
					sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY
				);
			
			preparedStatement.setInt(1, regularReadingID);
			logger.info(preparedStatement.toString());
			resultSet = preparedStatement.executeQuery();
			//place the cursor at the beginning of the result set. Otherwise it can not start reading the result set. 
			resultSet.beforeFirst();
			
			while (resultSet.next())
			{	
				listOfAllReadingsIncludedInRegularReadingByRegularReadingID.add(fillReadingIncludedInRegularReadingWithDataRetrievedFromDB());
			}
		} 
		catch (SQLException e) 
		{
			logger.error(e.getMessage());
		}
		finally
		{
			ConnectionManager.getInstance().closeStatement(preparedStatement);
			ConnectionManager.getInstance().closeResultSet(resultSet);
		}
		
		return listOfAllReadingsIncludedInRegularReadingByRegularReadingID;
	}
	
	//-----------------HELPER METHODS----------------------------------
	
	private ElectricityMeterIncludedInRegularReading fillReadingIncludedInRegularReadingWithDataRetrievedFromDB()
	{
		ElectricityMeterIncludedInRegularReading readingIncludedInRegularReadingRetrieved = null;
		
		try 
		{
			readingIncludedInRegularReadingRetrieved = new ElectricityMeterIncludedInRegularReading();
			readingIncludedInRegularReadingRetrieved.setRegularReadingFK(resultSet.getInt("regularReadingFK"));
			readingIncludedInRegularReadingRetrieved.setElectricityMeterFK(resultSet.getInt("electricityMeterFK"));
			readingIncludedInRegularReadingRetrieved.setElectricityMeterReadingFK((Integer) resultSet.getObject("electricityMeterReadingFK"));
			readingIncludedInRegularReadingRetrieved.setHasReadingBeenTaken(resultSet.getBoolean("hasReadingBeenTaken"));
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		
		return readingIncludedInRegularReadingRetrieved;
	}
}
